package sisgel.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import sisgel.Reservation;
import sisgel.user.Monitor;
import sisgel.user.Professor;
import sisgel.user.Student;
import sisgel.user.User;

public class DataBaseSQL {
	
	private static final String BATABASE_URL = "jdbc:mysql://localhost/sisgel";
	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private static final String USERNAME = "admin";
	private static final String PASSWORD = "admin";
	
	private static Connection connection;
	private static Statement statement;
	private static ResultSet resultSet;
	private static ResultSetMetaData metaData;
	
	public static void connect() {
		try {
			Class.forName(JDBC_DRIVER);
			
			connection = DriverManager.getConnection(BATABASE_URL,USERNAME,PASSWORD);
			statement = connection.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void disconnect() {
		try {
			connection.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static int saveUser(User user, String password) {
		connect();
		
		int userId = user.getUserId();
		String command;
		
		if (userId == -1) {
			command = "INSERT INTO usuario VALUES (NULL, '" + user.getUserName() + "', '" + 
				user.getLogin() + "', '" + password + "', '" + user.getPhone() + "', '" + 
				user.getEmail();
			String command2 = null;
			
			if (user instanceof Student) {
				command = command + "', 4)";
				command2 = "INSERT INTO aluno VALUES (" + ((Student) user).getIdNumber() + ", '" + 
					((Student) user).getCourse() + "', ";
				
			} else if (user instanceof Professor) {
				command = command + "', 3)"; 
				command2 = "INSERT INTO professor VALUES (" + ((Professor) user).getSiape() + ", '" + 
					((Professor) user).getWorkPhone() + "', '" + ((Professor) user).getLotacao() + "', ";
				
			} else if (user instanceof Monitor) {
				command = command + "', 2)"; 
				command2 = "INSERT INTO tecnico VALUES (" + ((Monitor) user).getIdNumber() + ", '" + 
					((Monitor) user).getIdLab() + "', ";
				
			} else {
				command = command + "', 1)";
			}
			
			try {
				if (!statement.execute(command, Statement.RETURN_GENERATED_KEYS)) {
					resultSet = statement.getGeneratedKeys();
					resultSet.next();
					
					userId = resultSet.getInt(1);
					
					if (command2 != null) {
						command = command2 + userId + ")";
						statement.execute(command);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			command = "UPDATE usuario SET nomeUsuario='" + user.getUserName() + "', login='"+ 
				user.getLogin() + "', senha='" + password + "', telefone='" + user.getPhone() + 
				"', email='" + user.getEmail() + "' WHERE idUsuario=" + userId;
			
			if (user instanceof Student) {
				command = command + ";UPDATE aluno SET matricula=" + ((Student) user).getIdNumber() +
					", curso='" + ((Student) user).getCourse() + "' WHERE idUsuario=" + userId;
				
			} else if (user instanceof Professor) {
				command = command + ";UPDATE professor SET siape=" + ((Professor) user).getSiape() +
					", telefoneTrabalho='" + ((Professor) user).getWorkPhone() + "', lotacao='" +
					((Professor) user).getLotacao() + "' WHERE idUsuario=" + userId;
				
			} else if (user instanceof Monitor) {
				command = command + ";UPDATE tecnico SET matricula=" + ((Monitor) user).getIdNumber() +
					", idLaboratorio='" + ((Monitor) user).getIdLab() + "'WHERE idUsuario=" + userId;
				
			}
			
			try {
				if (statement.executeUpdate(command) == 1) {
					userId = 0;
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
		}
		
		disconnect();
		
		return userId;
	}
	
	public static User getUser(String login, String password) {
		connect();
		
		User user = null;
		String command = "SELECT * FROM usuario WHERE login='" + login + "' AND senha='" + password + "'";
		
		try {
			resultSet = statement.executeQuery(command);
			resultSet.next();
			
			byte permission = resultSet.getByte(7);
			
			int userId = resultSet.getInt(1);
			String userName = resultSet.getString(2);
			String phone = resultSet.getString(5);
			String email = resultSet.getString(6);
			
			command = "SELECT * FROM ";
			
			if (permission == 4) {
				command = command + "aluno WHERE idUsuario=" + userId;
				
				resultSet = statement.executeQuery(command);
				resultSet.next();
				
				user = new Student(userId, userName, login, password, phone, email, resultSet.getInt(1), resultSet.getString(2));
				
			} else if (permission == 3) {
				command = command + "professor WHERE idUsuario=" + userId;
				
				resultSet = statement.executeQuery(command);
				resultSet.next();
				
				user = new Professor(userId, userName, login, password, phone, email, resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
				
			} else if (permission == 2) {
				command = command + "tecnico WHERE idUsuario=" + userId;
				
				resultSet = statement.executeQuery(command);
				resultSet.next();
				
				user = new Monitor(userId, userName, login, password, phone, email, resultSet.getInt(1), resultSet.getString(2));
				
			} else if (permission == 1) {
				user = new User(userId, userName, login, password, phone, email);
			}
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
		disconnect();
		
		return user;
		
	}
	
	public static int updateReservation (Reservation reservation){
		connect();
		
		int reservationId = reservation.getReservationId();
		String command;
		
		if (reservationId == -1) {
			command = "INSERT INTO reserva (userId, itemId, labId, reservationBegin, reservationEnd) " 
				+ "VALUES ('" + reservation.getUserId() + "','" + reservation.getItemId() + "','" 
				+ reservation.getLabId() + "','" + reservation.getReservationBegin() + "','" 
				+ reservation.getReservationEnd() + "')";
			
			try {
				if (statement.executeUpdate(command, Statement.RETURN_GENERATED_KEYS) == 1) {
					resultSet = statement.getGeneratedKeys();
					resultSet.next();
					
					reservationId = resultSet.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			command = "UPDATE reserva SET userId='" + reservation.getUserId() + "', itemId='" 
			+ reservation.getItemId() + "', labId='" + reservation.getLabId() + "', reservationBegin='" 
			+ reservation.getReservationBegin() + "', reservationEnd='"	+ reservation.getReservationEnd() 
			+ "' WHERE reservationId=" + reservationId + ";";
						
			try {
				if (statement.executeUpdate(command) == 1) {
					reservationId = 0;
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
		}
		
		disconnect();
		
		return reservationId;
		
	}
	
	public static Reservation searchReservation (int reservationId){
		connect();
		
		Reservation reservation = null;
		
		String command = "SELECT * FROM reserva WHERE reservationId='" + reservationId + "'";
		
		try {
			resultSet = statement.executeQuery(command);
			metaData = resultSet.getMetaData();
			resultSet.next();
			
			reservation = new Reservation(resultSet.getInt(1), resultSet.getInt(2), resultSet.getString(3), resultSet.getString(4), 
					resultSet.getTimestamp(5).getTime(), resultSet.getTimestamp(6).getTime(), resultSet.getTimestamp(7).getTime());
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
		disconnect();
		
		
		
		return reservation;
	}
	
	public static void printResultSet(ResultSet resultSet) {
		int number;
		
		try {
			metaData = resultSet.getMetaData();
			number = metaData.getColumnCount();
			
			for (int i = 1; i <= number; i++)
				System.out.print(metaData.getColumnName(i));
			System.out.println();
			
			while(resultSet.next()) {
				for (int i = 1; i <= number; i++)
					System.out.print(resultSet.getObject(i));
				System.out.println();
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String arg[]) {
		//new Student("Thiago", "teste", "teste", "3456-7890", "thiago@email.com", 23456789, "Sou Fodismo");
	}
	
}